/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.thienloc.omap.session;

import com.thienloc.omap.entity.RequestQueueHist;
import com.thienloc.omap.entity.TrafficReport;
import com.thienloc.omap.entity.report.Production;
import com.thienloc.omap.entity.report.ServiceProduction;
import com.thienloc.omap.entity.report.ShortCodeProduction;
import com.thienloc.omap.entity.report.TimeProduction;
import com.thienloc.omap.util.Constant;
import java.io.File;
import java.io.FileReader;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.TreeMap;
import org.slf4j.LoggerFactory;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TypedQuery;
import org.apache.commons.io.FileUtils;
import org.slf4j.Logger;

/**
 *
 * @author GiangLT
 */
@Stateless
public class RequestQueueHistFacade extends AbstractFacade<RequestQueueHist> {

    private final Logger logger = LoggerFactory.getLogger(this.getClass().getSimpleName());
    @PersistenceContext(unitName = "omapPU")
    private EntityManager em;

    @Override
    protected EntityManager getEntityManager() {
        return em;
    }

    public RequestQueueHistFacade() {
        super(RequestQueueHist.class);
    }

    public List<RequestQueueHist> findByMsisdn(String msisdn) {
        try {
            TypedQuery<RequestQueueHist> q = em.createNamedQuery("RequestQueueHist.findByMsisdn", RequestQueueHist.class);
            q.setParameter("msisdn", msisdn);
            List<RequestQueueHist> result = q.getResultList();
            return result;
        } catch (NoResultException ex) {
            return new ArrayList<RequestQueueHist>();
        } catch (Exception ex) {
            logger.error("", ex);
            return new ArrayList<RequestQueueHist>();
        }
    }

    public List<RequestQueueHist> findByMsisdnInDateRange(String msisdn, Date startDate, Date endDate) {
        try {
            TypedQuery<RequestQueueHist> q = em.createNamedQuery("RequestQueueHist.findByMsisdnInDateRange", RequestQueueHist.class);
            q.setParameter("startDate", startDate);
            q.setParameter("endDate", endDate);
            q.setParameter("msisdn", "%" + msisdn + "%");
            List<RequestQueueHist> result = q.getResultList();
            return result;
        } catch (NoResultException ex) {
            return new ArrayList<RequestQueueHist>();
        } catch (Exception ex) {
            logger.error("", ex);
            return new ArrayList<RequestQueueHist>();
        }
    }
    
    public List<RequestQueueHist> findByMsisdnBeforeStartDate(String msisdn, Date startDate) {
        try {
            TypedQuery<RequestQueueHist> q = em.createNamedQuery("RequestQueueHist.findByMsisdnBeforeStartDate", RequestQueueHist.class);
            q.setParameter("startDate", startDate);
            q.setParameter("msisdn", "%" + msisdn + "%");
            List<RequestQueueHist> result = q.getResultList();
            return result;
        } catch (NoResultException ex) {
            return new ArrayList<RequestQueueHist>();
        } catch (Exception ex) {
            logger.error("", ex);
            return new ArrayList<RequestQueueHist>();
        }
    }

    public List<String> findAvaiableServices() {
        try {
            Query q = em.createQuery("SELECT distinct k.serviceName FROM RequestQueueHist k");
            List<String> result = (List<String>) q.getResultList();
            return result;
        } catch (NoResultException ex) {
            return new ArrayList<String>();
        } catch (Exception ex) {
            logger.error("", ex);
            return new ArrayList<String>();
        }
    }

    public List<String> findTxtBaseUsers(List<String> syntaxList, Date startTime, Date endTime, String provider) {
        try {
            File configFile = new File(Constant.CONFIG_FOLDER + Constant.CONFIG_FILE_NAME);
            Properties properties = new Properties();
            properties.load(new FileReader(configFile));

            String jpqlFilePath = properties.getProperty("jpqlFile.txtbase.hist");
            File jpqlFile = new File(jpqlFilePath);
            List<String> strQuerys = FileUtils.readLines(jpqlFile);
            StringBuilder sb = new StringBuilder();
            for (String query : strQuerys) {
                sb.append(query.trim());
                sb.append(" ");
            }

            Query q = em.createQuery(sb.toString(), String.class);
            q.setParameter("startTime", startTime);
            q.setParameter("endTime", endTime);
            q.setParameter("provider", provider);
            q.setParameter("syntaxList", syntaxList);

            List<String> result = (List<String>) q.getResultList();
            return result;
        } catch (NoResultException ex) {
            return new ArrayList<String>();
        } catch (Exception ex) {
            logger.error("", ex);
            return new ArrayList<String>();
        }
    }

    public List<String> findUsersBySyntax(List<String> syntaxList, Date startTime, Date endTime, String serviceName, String provider) {
        try {
            File configFile = new File(Constant.CONFIG_FOLDER + Constant.CONFIG_FILE_NAME);
            Properties properties = new Properties();
            properties.load(new FileReader(configFile));

            String jpqlFilePath = properties.getProperty("jpqlFile.bySyntax.hist");
            File jpqlFile = new File(jpqlFilePath);
            List<String> strQuerys = FileUtils.readLines(jpqlFile);
            StringBuilder sb = new StringBuilder();
            for (String query : strQuerys) {
                sb.append(query.trim());
                sb.append(" ");
            }

            Query q = em.createQuery(sb.toString(), String.class);
            q.setParameter("startTime", startTime);
            q.setParameter("endTime", endTime);
            q.setParameter("serviceName", serviceName);
            q.setParameter("provider", provider);
            q.setParameter("syntaxList", syntaxList);

            List<String> result = (List<String>) q.getResultList();
            return result;
        } catch (NoResultException ex) {
            return new ArrayList<String>();
        } catch (Exception ex) {
            logger.error("", ex);
            return new ArrayList<String>();
        }
    }

    public List<String> find(String region, String serviceName, Date startTime, Date endTime, String provider) {
        try {
            File configFile = new File(Constant.CONFIG_FOLDER + Constant.CONFIG_FILE_NAME);
            Properties properties = new Properties();
            properties.load(new FileReader(configFile));

            String jpqlFilePath = properties.getProperty("jpqlFile." + serviceName + "." + region + ".history");
            File jpqlFile = new File(jpqlFilePath);
            List<String> strQuerys = FileUtils.readLines(jpqlFile);
            StringBuilder sb = new StringBuilder();
            for (String query : strQuerys) {
                sb.append(query.trim());
                sb.append(" ");
            }

            Query q = em.createQuery(sb.toString(), String.class);
            q.setParameter("startTime", startTime);
            q.setParameter("endTime", endTime);
            q.setParameter("serviceName", serviceName);
            q.setParameter("provider", provider);
            List<String> result = (List<String>) q.getResultList();
            return result;
        } catch (NoResultException ex) {
            return new ArrayList<String>();
        } catch (Exception ex) {
            logger.error("", ex);
            return new ArrayList<String>();
        }
    }

    public List<RequestQueueHist> findByServiceNamesAndStatus(List<String> serviceNames, List<String> statuses) {
        try {
            StringBuilder query = new StringBuilder("SELECT r FROM RequestQueueHist r WHERE (r.serviceName IN ('");

            int serviceCnt = 0;
            for (String serviceName : serviceNames) {
                if (!serviceName.equals("None")) {
                    query.append(serviceName);
                    query.append("', '");
                    serviceCnt++;
                }
            }
            if (serviceCnt > 0) {
                query.delete(query.lastIndexOf(", '"), query.length());
                query.append(")");
            } else {
                query.append("')");
            }

            if (serviceNames.contains("None")) {
                query.append(" OR r.serviceName IS NULL");
            }
            query.append(") AND r.status IN ('");
            for (String status : statuses) {
                query.append(status);
                query.append("', '");
            }
            query.delete(query.lastIndexOf(", '"), query.length());
            query.append(")");

            TypedQuery<RequestQueueHist> q = em.createQuery(query.toString(), RequestQueueHist.class);
            List<RequestQueueHist> result = q.getResultList();
            return result;
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            logger.error("", ex);
            return null;
        }
    }

    public List<TrafficReport> getTrafficReport(Date startDate, Date endDate, String serviceName, String cpNumber) {
        try {
            String queryStr =
                    "select trunc(rh.received_time), rh.service_name, rh.cp_number, count(trunc(rh.received_time))"
                    + " from request_queue_hist rh"
                    + " where rh.received_time between ? and ?"
                    + " and rh.service_name like ?"
                    + " and rh.cp_number = ?"
                    + " group by trunc(rh.received_time), rh.service_name, rh.cp_number";
            Query query = em.createNativeQuery(queryStr);
            query.setParameter(1, startDate);
            query.setParameter(2, endDate);
            query.setParameter(3, "%" + serviceName + "%");
            query.setParameter(4, cpNumber);

            List result = query.getResultList();
            List<TrafficReport> trafficReports = new ArrayList<TrafficReport>(result.size());
            Iterator it = result.iterator();
            while (it.hasNext()) {
                Object[] fields = (Object[]) it.next();
                TrafficReport report = new TrafficReport();
                report.setDate((Date) fields[0]);
                report.setService((String) fields[1]);
                report.setCpNumber((String) fields[2]);
                report.setTotalMessage(((BigDecimal) fields[3]).intValue());

                trafficReports.add(report);
            }
            return trafficReports;
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            logger.error("", ex);
            return new ArrayList<TrafficReport>(0);
        }
    }

    public List<String> getDeliverProviders() throws Exception {
        try {
            TypedQuery<String> q = em.createNamedQuery("RequestQueueHist.getDeliverProviders", String.class);
            List<String> result = q.getResultList();
            return result;
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            logger.error("", ex);
            return new ArrayList<String>(0);
        }
    }

    public List<String> getServiceNames() throws Exception {
        try {
            TypedQuery<String> q = em.createNamedQuery("RequestQueueHist.getServiceNames", String.class);
            List<String> result = q.getResultList();
            return result;
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            logger.error("", ex);
            return new ArrayList<String>(0);
        }
    }

    public List<String> getCpNumbers() throws Exception {
        try {
            TypedQuery<String> q = em.createNamedQuery("RequestQueueHist.getCpNumbers", String.class);
            List<String> result = q.getResultList();
            return result;
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            logger.error("", ex);
            return new ArrayList<String>(0);
        }
    }

    public List<Production> countByService(Date startDate, Date endDate, String serviceName, String datePattern) {
        try {
            String queryStr =
                    "SELECT TRUNC(received_time), COUNT(service_name)"
                    + " FROM request_queue_hist"
                    + " WHERE (received_time BETWEEN ? AND ?)"
                    + " AND service_name LIKE ?"
                    + " GROUP BY TRUNC(received_time)";
            Query query = em.createNativeQuery(queryStr);
            query.setParameter(1, startDate);
            query.setParameter(2, endDate);
            query.setParameter(3, "%" + serviceName + "%");

            List result = query.getResultList();
            List<Production> productionReports = new ArrayList<Production>(result.size());
            Iterator it = result.iterator();
            while (it.hasNext()) {
                Object[] fields = (Object[]) it.next();
                Production production = new Production();
                Timestamp timestamp = (Timestamp) fields[0];
                DateFormat df = new SimpleDateFormat(datePattern);
                production.setDatetime(df.format(timestamp));
                production.setServiceName(serviceName);
                production.setTotal(((BigDecimal) fields[1]).intValue());

                productionReports.add(production);
            }
            return productionReports;
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            logger.error("", ex);
            return new ArrayList<Production>(0);
        }
    }

    public Production countByService(Date date, String serviceName, String datePattern) {
        DateFormat df = new SimpleDateFormat(datePattern);

        try {
            String queryStr =
                    "SELECT TRUNC(received_time), COUNT(service_name)"
                    + " FROM request_queue_hist"
                    + " WHERE (received_time BETWEEN ? AND ?)"
                    + " AND service_name = ?"
                    + " GROUP BY TRUNC(received_time)";
            Query query = em.createNativeQuery(queryStr);
            Date startDate = new Date(date.getTime());
            startDate.setHours(0);
            startDate.setMinutes(0);
            startDate.setSeconds(0);
            query.setParameter(1, startDate);

            Date endDate = new Date(date.getTime());
            endDate.setHours(23);
            endDate.setMinutes(59);
            endDate.setSeconds(59);
            query.setParameter(2, endDate);

            query.setParameter(3, serviceName);

            Object[] fields = (Object[]) query.getSingleResult();
            Production production = new Production();
            Timestamp timestamp = (Timestamp) fields[0];
            production.setDatetime(df.format(timestamp));
            production.setServiceName(serviceName);
            production.setTotal(((BigDecimal) fields[1]).intValue());

            return production;
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            logger.error("", ex);
            return new Production(df.format(date), serviceName, "", "", 0);
        }
    }

    public List<ShortCodeProduction> countByShortCode(Date date, String sqlDatePattern) {
        DateFormat df = new SimpleDateFormat(sqlDatePattern);

        try {
            StringBuilder queryStr = new StringBuilder("SELECT ");
            queryStr.append(" DECODE(deliver_provider, 'smsgate_viettel6771', 'Viettel', 'smsgate_mobifone6771', 'Mobifone', 'Vinaphone') deliver_provider, ");
            queryStr.append("SUM (\"6071\") \"6071\" , SUM (\"6171\") \"6171\", SUM (\"6271\") \"6271\", ");
            queryStr.append(" SUM (\"6371\") \"6371\", SUM (\"6471\") \"6471\", SUM (\"6571\") \"6571\", SUM (\"6671\") \"6671\", ");
            queryStr.append(" SUM (\"6771\") \"6771\" FROM ( ");

            queryStr.append(" SELECT deliver_provider, ");
            queryStr.append(" COUNT (*) \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist");
            queryStr.append(" WHERE to_char(received_time,'").append(sqlDatePattern).append("') = '").append(df.format(date)).append("' AND cp_number like  '%6071'");
            queryStr.append(" GROUP BY deliver_provider");
            queryStr.append(" union all ");

            queryStr.append(" SELECT deliver_provider, ");
            queryStr.append(" 0 \"6071\", COUNT (*) \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist");
            queryStr.append(" WHERE to_char(received_time,'").append(sqlDatePattern).append("') = '").append(df.format(date)).append("' AND cp_number like  '%6171'");
            queryStr.append(" GROUP BY deliver_provider");
            queryStr.append(" union all ");

            queryStr.append(" SELECT deliver_provider, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", COUNT (*) \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist");
            queryStr.append(" WHERE to_char(received_time,'").append(sqlDatePattern).append("') = '").append(df.format(date)).append("' AND cp_number like  '%6271'");
            queryStr.append(" GROUP BY deliver_provider");
            queryStr.append(" union all ");

            queryStr.append(" SELECT deliver_provider, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", COUNT (*) \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist");
            queryStr.append(" WHERE to_char(received_time,'").append(sqlDatePattern).append("') = '").append(df.format(date)).append("' AND cp_number like  '%6371'");
            queryStr.append(" GROUP BY deliver_provider");
            queryStr.append(" union all ");

            queryStr.append(" SELECT deliver_provider, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", COUNT (*) \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist");
            queryStr.append(" WHERE to_char(received_time,'").append(sqlDatePattern).append("') = '").append(df.format(date)).append("' AND cp_number like  '%6471'");
            queryStr.append(" GROUP BY deliver_provider");
            queryStr.append(" union all ");

            queryStr.append(" SELECT deliver_provider, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",COUNT (*) \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist");
            queryStr.append(" WHERE to_char(received_time,'").append(sqlDatePattern).append("') = '").append(df.format(date)).append("' AND cp_number like  '%6571'");
            queryStr.append(" GROUP BY deliver_provider");
            queryStr.append(" union all ");

            queryStr.append(" SELECT deliver_provider, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", COUNT (*) \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist");
            queryStr.append(" WHERE to_char(received_time,'").append(sqlDatePattern).append("') = '").append(df.format(date)).append("' AND cp_number like  '%6671'");
            queryStr.append(" GROUP BY deliver_provider");
            queryStr.append(" union all ");

            queryStr.append(" SELECT deliver_provider, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", COUNT (*) \"6771\"");
            queryStr.append(" FROM request_queue_hist");
            queryStr.append(" WHERE to_char(received_time,'").append(sqlDatePattern).append("') = '").append(df.format(date)).append("' AND cp_number like  '%6771'");
            queryStr.append(" GROUP BY deliver_provider");

            queryStr.append(") GROUP BY deliver_provider");

            Query query = em.createNativeQuery(queryStr.toString());
            List resultList = query.getResultList();
            List<ShortCodeProduction> result = new ArrayList<ShortCodeProduction>();
            for (Object row : resultList) {
                Object[] fields = (Object[]) row;
                ShortCodeProduction production = new ShortCodeProduction();
                production.setOpertator(fields[0].toString());
                production.setP6071(((BigDecimal) fields[1]).intValue());
                production.setP6171(((BigDecimal) fields[2]).intValue());
                production.setP6271(((BigDecimal) fields[3]).intValue());
                production.setP6371(((BigDecimal) fields[4]).intValue());
                production.setP6471(((BigDecimal) fields[5]).intValue());
                production.setP6571(((BigDecimal) fields[6]).intValue());
                production.setP6671(((BigDecimal) fields[7]).intValue());
                production.setP6771(((BigDecimal) fields[8]).intValue());
                result.add(production);
            }

            return result;
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            logger.error("", ex);
            return null;
        }
    }

    public List<ServiceProduction> countByService(String[] services, Date date, String sqlDatePattern) {
        DateFormat df = new SimpleDateFormat(sqlDatePattern);

        try {
            StringBuilder queryStr = new StringBuilder("SELECT ");
            queryStr.append(" deliver_provider,");
            for (String service : services) {
                queryStr.append(" SUM(").append(service).append(") ").append(service).append(", ");
            }
            queryStr.delete(queryStr.lastIndexOf(", "), queryStr.length());
            queryStr.append(" FROM ( ");

            for (String service : services) {
                queryStr.append(" SELECT deliver_provider, ");
                for (String serviceInLoop : services) {
                    if (serviceInLoop.equals(service)) {
                        queryStr.append(" COUNT(*) ");
                    } else {
                        queryStr.append(" 0 ");
                    }
                    queryStr.append(serviceInLoop).append(", ");
                }

                queryStr.delete(queryStr.lastIndexOf(", "), queryStr.length());

                queryStr.append(" FROM request_queue_hist");
                queryStr.append(" WHERE received_time like '").append(df.format(date)).append("' AND service_name = '").append(service).append("'");
                queryStr.append(" GROUP BY deliver_provider");
                queryStr.append(" union all ");
            }
            queryStr.delete(queryStr.lastIndexOf(" union all "), queryStr.length());
            queryStr.append(") GROUP BY deliver_provider");

            Query query = em.createNativeQuery(queryStr.toString());
            List resultList = query.getResultList();
            List<ServiceProduction> result = new ArrayList<ServiceProduction>();
            for (Object row : resultList) {
                Object[] fields = (Object[]) row;
                ServiceProduction production = new ServiceProduction();
                production.setOpertator(fields[0].toString());
                for (int i = 0; i < services.length; i++) {
                    production.put(services[i], ((BigDecimal) fields[i + 1]).intValue());
                }
                result.add(production);
            }

            return result;
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            logger.error("", ex);
            return null;
        }
    }

    public List<TimeProduction> countByTime(String[] dates, String datePattern) {
        StringBuilder dateCriteria = new StringBuilder();
        dateCriteria.append(" to_char(received_time, '").append(datePattern).append("') in (");
        for (String date : dates) {
            dateCriteria.append("'").append(date.trim()).append("', ");
        }
        dateCriteria.delete(dateCriteria.lastIndexOf("', "), dateCriteria.length());
        dateCriteria.append(") ");

        try {
            StringBuilder queryStr = new StringBuilder("SELECT ");
            queryStr.append(" received_time, SUM (\"6071\") \"6071\" , SUM (\"6171\") \"6171\", SUM (\"6271\") \"6271\", ");
            queryStr.append(" SUM (\"6371\") \"6371\", SUM (\"6471\") \"6471\", SUM (\"6571\") \"6571\", SUM (\"6671\") \"6671\", ");
            queryStr.append(" SUM (\"6771\") \"6771\" FROM ( ");

            queryStr.append(" SELECT to_char(received_time, '").append(datePattern).append("') received_time, ");
            queryStr.append(" COUNT (*) \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist WHERE ");
            queryStr.append(dateCriteria);
            queryStr.append(" AND cp_number like  '%6071'");
            queryStr.append(" GROUP BY to_char(received_time, '").append(datePattern).append("')");
            queryStr.append(" union all ");

            queryStr.append(" SELECT to_char(received_time, '").append(datePattern).append("') received_time, ");
            queryStr.append(" 0 \"6071\", COUNT (*) \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist WHERE ");
            queryStr.append(dateCriteria);
            queryStr.append(" AND cp_number like  '%6171'");
            queryStr.append(" GROUP BY to_char(received_time, '").append(datePattern).append("')");
            queryStr.append(" union all ");

            queryStr.append(" SELECT to_char(received_time, '").append(datePattern).append("') received_time, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", COUNT (*) \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist WHERE ");
            queryStr.append(dateCriteria);
            queryStr.append(" AND cp_number like  '%6271'");
            queryStr.append(" GROUP BY to_char(received_time, '").append(datePattern).append("')");
            queryStr.append(" union all ");

            queryStr.append(" SELECT to_char(received_time, '").append(datePattern).append("') received_time, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", COUNT (*) \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist WHERE ");
            queryStr.append(dateCriteria);
            queryStr.append(" AND cp_number like  '%6371'");
            queryStr.append(" GROUP BY to_char(received_time, '").append(datePattern).append("')");
            queryStr.append(" union all ");

            queryStr.append(" SELECT to_char(received_time, '").append(datePattern).append("') received_time, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", COUNT (*) \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist WHERE ");
            queryStr.append(dateCriteria);
            queryStr.append(" AND cp_number like  '%6471'");
            queryStr.append(" GROUP BY to_char(received_time, '").append(datePattern).append("')");
            queryStr.append(" union all ");

            queryStr.append(" SELECT to_char(received_time, '").append(datePattern).append("') received_time, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",COUNT (*) \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist WHERE ");
            queryStr.append(dateCriteria);
            queryStr.append(" AND cp_number like  '%6571'");
            queryStr.append(" GROUP BY to_char(received_time, '").append(datePattern).append("')");
            queryStr.append(" union all ");

            queryStr.append(" SELECT to_char(received_time, '").append(datePattern).append("') received_time, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", COUNT (*) \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist WHERE ");
            queryStr.append(dateCriteria);
            queryStr.append(" AND cp_number like  '%6671'");
            queryStr.append(" GROUP BY to_char(received_time, '").append(datePattern).append("')");
            queryStr.append(" union all ");

            queryStr.append(" SELECT to_char(received_time, '").append(datePattern).append("') received_time, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", COUNT (*) \"6771\"");
            queryStr.append(" FROM request_queue_hist WHERE ");
            queryStr.append(dateCriteria);
            queryStr.append(" AND cp_number like  '%6771'");
            queryStr.append(" GROUP BY to_char(received_time, '").append(datePattern).append("')");

            queryStr.append(") GROUP BY received_time");

            Query query = em.createNativeQuery(queryStr.toString());
            List resultList = query.getResultList();
            List<TimeProduction> result = new ArrayList<TimeProduction>();
            for (Object row : resultList) {
                Object[] fields = (Object[]) row;
                TimeProduction production = new TimeProduction();
                production.setDate(fields[0].toString());
                production.setP6071(((BigDecimal) fields[1]).intValue());
                production.setP6171(((BigDecimal) fields[2]).intValue());
                production.setP6271(((BigDecimal) fields[3]).intValue());
                production.setP6371(((BigDecimal) fields[4]).intValue());
                production.setP6471(((BigDecimal) fields[5]).intValue());
                production.setP6571(((BigDecimal) fields[6]).intValue());
                production.setP6671(((BigDecimal) fields[7]).intValue());
                production.setP6771(((BigDecimal) fields[8]).intValue());
                result.add(production);
            }

            return result;
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            logger.error("", ex);
            return null;
        }
    }

    public Map<String, Integer> countProductionOfOperators(Date startTime, Date endTime, String cpNumber, List<String> deliverProviders) {
        try {
            StringBuilder queryStr = new StringBuilder();
            queryStr.append(" SELECT COUNT(r.request_id), TRIM(UPPER(r.request_detail)) ");
            queryStr.append(" FROM request_queue_hist r ");
            queryStr.append(" WHERE (r.received_time BETWEEN ? AND ?) ");
            queryStr.append(" AND r.cp_number = ? ");
            queryStr.append(" AND service_name IS NOT NULL ");
            
            queryStr.append(" AND r.deliver_provider IN (");
            for (String deliverProvider : deliverProviders) {
                queryStr.append("'").append(deliverProvider).append("',");
            }
            queryStr.delete(queryStr.lastIndexOf(","), queryStr.length());
            queryStr.append(") ");

            queryStr.append(" GROUP BY TRIM(UPPER(r.request_detail)) ");

            logger.trace(queryStr.toString());
            Query query = em.createNativeQuery(queryStr.toString());
            query.setParameter(1, startTime);
            query.setParameter(2, endTime);
            query.setParameter(3, cpNumber);

            List result = query.getResultList();
            Map<String, Integer> resultMap = new TreeMap<String, Integer>();
            Iterator it = result.iterator();
            while (it.hasNext()) {
                Object[] fields = (Object[]) it.next();
                String requestDetail = (String) fields[1];
                if (requestDetail != null && !requestDetail.trim().isEmpty()) {
                    Integer count = ((BigDecimal) fields[0]).intValue();
                    if (resultMap.containsKey(requestDetail)) {
                        resultMap.put(requestDetail, count + resultMap.get(requestDetail));
                    } else {
                        resultMap.put(requestDetail, count);
                    }
                }
            }
            return resultMap;
        } catch (NoResultException ex) {
            return new HashMap<String, Integer>();
        } catch (Exception ex) {
            logger.error("", ex);
            return new HashMap<String, Integer>();
        }
    }
    
    public Map<String, Integer> countMoProduction(Date startTime, Date endTime, List<String> deliverProviders) {
        try {
            StringBuilder queryStr = new StringBuilder();
            queryStr.append(" SELECT COUNT(r.request_id), TRIM(UPPER(r.request_detail)) ");
            queryStr.append(" FROM request_queue_hist r ");
            queryStr.append(" WHERE (r.received_time BETWEEN ? AND ?) ");
            queryStr.append(" AND service_name IS NOT NULL ");

            queryStr.append(" AND r.deliver_provider IN (");
            for (String deliverProvider : deliverProviders) {
                queryStr.append("'").append(deliverProvider).append("',");
            }
            queryStr.delete(queryStr.lastIndexOf(","), queryStr.length());
            queryStr.append(") ");

            queryStr.append(" GROUP BY TRIM(UPPER(r.request_detail)) ");

            logger.trace(queryStr.toString());
            Query query = em.createNativeQuery(queryStr.toString());
            query.setParameter(1, startTime);
            query.setParameter(2, endTime);

            List result = query.getResultList();
            Map<String, Integer> resultMap = new TreeMap<String, Integer>();
            Iterator it = result.iterator();
            while (it.hasNext()) {
                Object[] fields = (Object[]) it.next();
                String requestDetail = (String) fields[1];
                if (requestDetail != null && !requestDetail.trim().isEmpty()) {
                    Integer count = ((BigDecimal) fields[0]).intValue();
                    if (resultMap.containsKey(requestDetail)) {
                        resultMap.put(requestDetail, count + resultMap.get(requestDetail));
                    } else {
                        resultMap.put(requestDetail, count);
                    }
                }
            }
            return resultMap;
        } catch (NoResultException ex) {
            return new HashMap<String, Integer>();
        } catch (Exception ex) {
            logger.error("", ex);
            return new HashMap<String, Integer>();
        }
    }

    public Integer countProductionOfOperators(String cpNumber, List<String> requestDetails, List<String> deliverProviders, Date startTime, Date endTime, Integer mdnSuffixStartRange, Integer mdnSuffixEndRange) {
        try {
            StringBuilder queryStr = new StringBuilder();
            queryStr.append(" SELECT COUNT(r.request_id) ");
            queryStr.append(" FROM request_queue_hist r ");
            queryStr.append(" WHERE (r.received_time BETWEEN ? AND ?) ");
            queryStr.append(" AND service_name IS NOT NULL ");

            int suffixLength = mdnSuffixEndRange.toString().length();
            queryStr.append(" AND TO_NUMBER(SUBSTR(msisdn, LENGTH(msisdn)-");
            queryStr.append(suffixLength - 1);
            queryStr.append(", ");
            queryStr.append(suffixLength);
            queryStr.append(")) >= ");
            queryStr.append(mdnSuffixStartRange);
            queryStr.append(" AND ");
            queryStr.append(" TO_NUMBER(SUBSTR(msisdn, LENGTH(msisdn)-");
            queryStr.append(suffixLength - 1);
            queryStr.append(", ");
            queryStr.append(suffixLength);
            queryStr.append(")) <= ");
            queryStr.append(mdnSuffixEndRange);

            queryStr.append(" AND TRIM(UPPER(r.request_detail)) IN ( ");
            for (int i = 0; i < requestDetails.size(); i++) {
//                queryStr.append("?,");
                queryStr.append("'").append(requestDetails.get(i)).append("',");
            }
            queryStr.deleteCharAt(queryStr.lastIndexOf(","));
            queryStr.append(" ) ");

            queryStr.append(" AND deliver_provider IN (");
            for (String deliverProvider : deliverProviders) {
                queryStr.append("'").append(deliverProvider).append("',");
            }
            queryStr.delete(queryStr.lastIndexOf(","), queryStr.length());
            queryStr.append(") ");
            queryStr.append(" AND cp_number = ? ");

            logger.trace(queryStr.toString());

            Query query = em.createNativeQuery(queryStr.toString());
            query.setParameter(1, startTime);
            query.setParameter(2, endTime);
            query.setParameter(3, cpNumber);
//            int paramIndex = 3;
//            for (String requestDetail : requestDetails) {
//                query.setParameter(paramIndex++, requestDetail);
//            }

            return ((BigDecimal) query.getSingleResult()).intValue();
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            logger.error("", ex);
            return null;
        }
    }

    public List countProductionOfService(List<String> deliverProviders, String sqlDatePattern, List<Date[]> timeRanges) {
        try {
            StringBuilder queryStr = new StringBuilder();
            SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
            for (Date[] timeRange : timeRanges) {
                String startTime = sdf.format(timeRange[0]);
                String endTime = sdf.format(timeRange[1]);
                queryStr.append("SELECT COUNT(r.request_id), TO_CHAR(r.received_time, '");
                queryStr.append(sqlDatePattern);
                queryStr.append("'), TRIM(UPPER(r.request_detail)) ");
                queryStr.append(" FROM request_queue_hist r ");
                queryStr.append(" WHERE (r.received_time BETWEEN TO_DATE('");
                queryStr.append(startTime);
                queryStr.append("', 'dd-mm-yyyy hh24:mi:ss') AND TO_DATE('");
                queryStr.append(endTime);
                queryStr.append("', 'dd-mm-yyyy hh24:mi:ss')) ");
                queryStr.append(" AND service_name IS NOT NULL ");
                queryStr.append(" AND deliver_provider IN (");
                for (String deliverProvider : deliverProviders) {
                    queryStr.append("'").append(deliverProvider).append("',");
                }
                queryStr.delete(queryStr.lastIndexOf(","), queryStr.length());
                queryStr.append(") ");
                queryStr.append(" GROUP BY TO_CHAR(received_time, '").append(sqlDatePattern).append("'), TRIM(UPPER(r.request_detail)) ");
                queryStr.append(" UNION ALL ");
            }
            queryStr.delete(queryStr.lastIndexOf(" UNION ALL "), queryStr.length());

            logger.trace(queryStr.toString());
            Query query = em.createNativeQuery(queryStr.toString());
            return query.getResultList();
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            logger.error("", ex);
            return null;
        }
    }
}
